package com.yinxing.webapi.code.controller.chart;

import com.aspose.cells.*;
import com.aspose.words.Document;
import com.yinxing.framework.domain.R;
import com.yinxing.webapi.code.viewobje.aspose.Individual;
import com.yinxing.webapi.code.viewobje.aspose.Person;
import com.yinxing.webapi.code.viewobje.aspose.Teacher;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
@RestController
@RequestMapping("/AsposeCellsApi")
public class AsposeCellsApi {

    /**
     * 测试数据
     */
    private List<Individual> individualList;

    /**
     * 系统启动后设置Aspose授权文件
     * 否则报表有水印(全局设置一次即可)
     */
    @PostConstruct
    public void initLicense() {
//        try {
//            new com.aspose.cells.License().setLicense(
//                    new ClassPathResource("aspose/Aspose.Total.Java.lic").getInputStream());
//            new com.aspose.words.License().setLicense(
//                    new ClassPathResource("aspose/Aspose.Total.Java.lic").getInputStream());
//            log.info("Aspose授权文件验证成功");
//        } catch (Exception e) {
//            log.error("Aspose授权文件验证失败", e);
//        }
        //初始化测试数据
        this.individualList = initData();
    }

    /**
     * 测试数据
     */
    private List<Individual> initData() {
        ArrayList<Individual> list = new ArrayList<>();
        list.add(new Individual("Todd J Sumrall", 23, "l2iqq3desth@linshiyouxiang.net", "4206 Hickory Ridge Drive", "702-614-6855", "130.2磅（59.2千克）", "female", "295-48-6787"));
        list.add(new Individual("Dennis L Brodsky", 33, "qxrkc7fyqk@claimab.com", "4486 Pheasant Ridge Road", "215-526-2406", "121.4磅（55.2千克）Sally C Newton", "female", "367-38-0267"));
        list.add(new Individual("Candie J Deloach", 18, "25t2oo6mj3ri@claimab.com", "4038 Golden Ridge Road", "518-272-4707", "155.5磅（70.7千克）", "male", "505-54-6365"));
        list.add(new Individual("Carmen W Keith", 23, "n1c3yfu7b4@claimab.com", "4892 Kelly Street", "704-927-4239", "121.4磅（55.2千克）Sally C Newton", "female", "367-38-0267"));
        list.add(new Individual("Theresa W Chavez", 23, "y303h0ebusk@thrubay.com", "4961 Woodbridge Lane", "313-544-6952", "149.2磅（67.8千克）", "male", "411-40-7611"));
        list.add(new Individual("Mathew L Vanover", 36, "jfz89cnbtmq@payspun.com", "1834 White Pine Lane", "540-710-0437", "200.0磅（90.9千克）", "male", "696-03-5628"));
        list.add(new Individual("Theresa W Chavez", 34, "gedqlm8a6km@thrubay.com", "4961 Woodbridge Lane", "313-544-6952", "121.4磅（55.2千克）", "female", "313-544-6952"));
        list.add(new Individual("Agnes B Jones", 22, "qo6hv6wu6u@linshiyouxiang.net", "3104 Wilkinson Court", "239-596-8511", "132.2磅（60.1千克）", "female", "239-596-8511"));
        list.add(new Individual("Theresa W Chavez", 21, "7oi9chmxrtm@iffymedia.com", "4961 Woodbridge Lane", "313-544-6952", "121.4磅（55.2千克）Sally C Newton", "female", "367-38-0267"));
        list.add(new Individual("Marcos S Rogers", 23, "vizyp4lce@linshiyouxiang.net", "1307 Valley Street", "856-757-1607", "205.0磅（93.2千克）", "male", "149-48-1764"));
        list.add(new Individual("Theresa W Chavez", 17, "nbmmuuswne@iffymedia.com", "4961 Woodbridge Lane", "313-544-6952", "121.4磅（55.2千克）Sally C Newton", "female", "313-544-6952"));
        list.add(new Individual("Eric H Bass", 21, "ik3xp9jpklk@linshiyouxiang.net", "176 Holly Street", "706-232-7347", "255.4磅（116.1千克）", "female", "722-112-7347"));
        list.add(new Individual("Mary J Barnhart", 42, "9m6l7245jzs@payspun.com", "176 Holly Street", "404-339-3342", "116.2磅（52.8千克）", "female", "252-66-1499"));
        list.add(new Individual("Georgina J Godfrey", 37, "lowchfmzy7@linshiyouxiang.net", "75 Fulford Road", "077-5710-3641", "170.9磅（77.7千克）", "female", "XE 07 57 85"));
        list.add(new Individual("Theresa W Chavez", 22, "bj7ks3gia4m@linshiyouxiang.net", "4961 Woodbridge Lane", "313-544-6952", "121.4磅（55.2千克）Sally C Newton", "female", "367-38-0267"));
        list.add(new Individual("Kenneth G Hass", 29, "8typ0utq0am@linshiyouxiang.net", "3909 Hood Avenue", "858-779-0443", "189.4磅（86.1千克）", "male", "617-82-3063"));
        list.add(new Individual("Eugene E Preston", 32, "9i2nn162d54@iffymedia.com", "39 Lightning Point Drive", "901-526-7583", "196.5磅（89.3千克）", "male", "410-22-5407"));
        list.add(new Individual("Tammy P Bailey", 32, "z30aor5g9x@iffymedia.com", "2777 Granville Lane", "973-472-1575", "202.2磅（91.9千克）", "female", "139-05-3733"));
        list.add(new Individual("Palawa Ni", 15, "irpqinqyaw@iubridge.com", "2422  Woodlawn Drive", "414-439-6747", "213.6磅（97.1千克）", "female", "006-69-9444"));
        list.add(new Individual("KatherineAnderson", 22, "5q69gibv7ip@temporary-mail.net", "338  Eagle Drive", "734-686-4603", "161.3lbs (73.2kg)", "female", "013-49-8729"));
        list.add(new Individual("Stein Lichner", 19, "dlfirxqqoo@iubridge.com", "2422  Woodlawn Drive", "812-552-5584", "147.9lbs (67.1kg)", "female", "812-552-5584"));
        list.add(new Individual("Ba Wei Zi", 17, "xktvkorszj@iubridge.com", "2839  Pearcy Avenue", "414-708-5934", "261lbs (118.4kg)", "female", "448-04-8672"));
        list.add(new Individual("Sill Tay", 24, "fnqwszfgxc@iubridge.com", "1314  Adams Avenue", "301-617-3777", "208.1lbs (94.4kg)", "female", "554-35-0573"));
        return list;
    }

    /**
     * 根据ID查询数据
     */
    private Individual findById(long id) {
        for (Individual inv: individualList) {
            if(id == inv.getId()) {
                return inv;
            }
        }
        return individualList.get(0);
    }

    /**
     * 查询数据集合
     */
    @GetMapping("/selectList")
    public R selectList() {
        return R.ok(individualList);
    }

    /**
     * 根据单元格名称解析Excel
     */
    @PostMapping("/uploadExcelUsingCellName")
    public R uploadExcelUsingCellName(@RequestParam("file") MultipartFile file) throws Exception {
        Workbook workbook = new Workbook(file.getInputStream());
        Worksheet worksheet = workbook.getWorksheets().get(0);
        Cells cells = worksheet.getCells();
        //可以根据名称读取单元格的值
        Cell cell = cells.get("A1");
        log.debug("Cell Value: " + cell.getValue());
        return R.ok(cell.getValue());
    }

    /**
     * 循环解析表格
     */
    @PostMapping("/uploadExcelParseList")
    public R uploadExcelParseList(@RequestParam("file") MultipartFile file) throws Exception {
        Workbook workbook = new Workbook(file.getInputStream());
        Worksheet worksheet = workbook.getWorksheets().get(0);
        Cells cells = worksheet.getCells();
        RowCollection rows = cells.getRows();
        for (int i = 0; i < rows.getCount(); i++) {
            Row row = rows.get(i);
            String c1 = row.get(0).getStringValue();
            String c2 = row.get(1).getStringValue();
            String c3 = row.get(2).getStringValue();
            System.out.println(c1 + " - " + c2 + " - " + c3);
        }
        return R.ok();
    }

    /**
     * 基于模板导出列表数据
     */
    @GetMapping("/exportList")
    public void exportList(HttpServletResponse response) throws Exception {
        //加载excel模板
        InputStream inputStream = new ClassPathResource("aspose/list.xlsx").getInputStream();
        Workbook workbook = new Workbook(inputStream);

        //excel数据填充工具
        WorkbookDesigner designer = new WorkbookDesigner();
        designer.setWorkbook(workbook);

        //填充数据
        designer.setDataSource("Individual", individualList);
        designer.process();

        //设置响应头为流下载
        response.setContentType("application/octet-stream");
        String fileName = new String("list-列表数据.xlsx".getBytes("gb2312"), "ISO8859-1");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);

        //输出到http流
        workbook.save(response.getOutputStream(), SaveFormat.XLSX);
    }

    /**
     * 下载列表模板
     */
    @GetMapping("/exportListTpl")
    public void exportListTpl(HttpServletResponse response) throws Exception {
        InputStream inputStream = new ClassPathResource("aspose/list.xlsx").getInputStream();
        response.setContentType("application/octet-stream");
        String fileName = new String("list-列表模板.xlsx".getBytes("gb2312"), "ISO8859-1");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        IOUtils.copy(inputStream, response.getOutputStream());
    }

    /**
     * 基于模板导出分组数据
     */
    @GetMapping("/exportGroupList")
    public void exportGroupList(HttpServletResponse response) throws Exception {
        Workbook workbook = new Workbook();

        //这里用代码定义模板 用户也可以用excel定义模板.
        Worksheet worksheet = workbook.getWorksheets().get(0);
        worksheet.getCells().get("A1").putValue("Teacher Name");
        worksheet.getCells().get("A2").putValue("&=Teacher.Name");
        worksheet.getCells().get("B1").putValue("Teacher Age");
        worksheet.getCells().get("B2").putValue("&=Teacher.Age");
        worksheet.getCells().get("C1").putValue("Student Name");
        worksheet.getCells().get("C2").putValue("&=Teacher.Students.Name");
        worksheet.getCells().get("D1").putValue("Student Age");
        worksheet.getCells().get("D2").putValue("&=Teacher.Students.Age");

        //A1:D1 添加样式
        Range range = worksheet.getCells().createRange("A1:D1");
        Style style = workbook.createStyle();
        style.getFont().setBold(true);
        style.setForegroundColor(Color.getYellow());
        style.setPattern(BackgroundType.SOLID);
        StyleFlag flag = new StyleFlag();
        flag.setAll(true);
        range.applyStyle(style, flag);

        //设计器
        WorkbookDesigner designer = new WorkbookDesigner();
        designer.setWorkbook(workbook);

        ArrayList<Teacher> list = new ArrayList<>();

        ArrayList<Person> students = new ArrayList<>();
        students.add(new Person("Chen Zhao", 14));
        students.add(new Person("Jamima Winfrey", 18));
        students.add(new Person("Reham Smith", 15));

        Teacher h1 = new Teacher("Mark John", 30, students);

        students = new ArrayList<>();
        students.add(new Person("Karishma Jathool", 16));
        students.add(new Person("Angela Rose", 13));
        students.add(new Person("Hina Khanna", 15));

        Teacher h2 = new Teacher("Masood Shankar", 40, students);

        list.add(h1);
        list.add(h2);

        //填充数据
        designer.setDataSource("Teacher", list);
        designer.process();
        worksheet.autoFitColumns();

        //设置响应头为流下载
        response.setContentType("application/octet-stream");
        String fileName = new String("list-分组显示.xlsx".getBytes("gb2312"), "ISO8859-1");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);

        //输出到http流
        workbook.save(response.getOutputStream(), SaveFormat.XLSX);
    }

    /**
     * 基于模板导出详细信息
     */
    @GetMapping("/exportDetail")
    public void exportDetail(HttpServletResponse response, long id, int format) throws Exception {
        //加载模板
        InputStream inputStream = new ClassPathResource("aspose/detail.xlsx").getInputStream();
        Workbook workbook = new Workbook(inputStream);

        //excel数据填充工具
        WorkbookDesigner designer = new WorkbookDesigner();
        designer.setWorkbook(workbook);

        //填充数据
        designer.setDataSource("Individual", findById(id));
        designer.process();

        //设置响应头为流下载
        response.setContentType("application/octet-stream");
        if (format == 0) {
            String fileName = new String("detail-详细信息.xlsx".getBytes("gb2312"), "ISO8859-1");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            workbook.save(response.getOutputStream(), SaveFormat.XLSX);
        } else {
            String fileName = new String("detail-详细信息.pdf".getBytes("gb2312"), "ISO8859-1");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            workbook.save(response.getOutputStream(), SaveFormat.PDF);
        }
    }

    /**
     * 下载模板
     */
    @GetMapping("/exportDetailTpl")
    public void exportDetailTpl(HttpServletResponse response) throws Exception {
        InputStream inputStream = new ClassPathResource("aspose/detail.xlsx").getInputStream();
        response.setContentType("application/octet-stream");
        String fileName = new String("detail-详细模板.xlsx".getBytes("gb2312"), "ISO8859-1");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        IOUtils.copy(inputStream, response.getOutputStream());
    }

    /**
     * 导出详细Word
     */
    @GetMapping("/exportDetailWord")
    public void exportDetailWord(HttpServletResponse response, long id) throws Exception {
        //加载Word模板
        //注意 注意 注意 注意 注意
        //文档中插入的属性如:<<name>> <<age>>等不是简单的字符串 而是在word中 插入 ->文档部件 ->域 -> MergeField
        InputStream inputStream = new ClassPathResource("aspose/detail.docx").getInputStream();
        Document document = new Document(inputStream);

        //把实体类字段转换格式
        Individual individual = findById(id);
        Map<String, Object> data = new HashMap<>();
        data.put("name", individual.getName());
        data.put("age", individual.getAge());
        data.put("email", individual.getEmail());
        data.put("address", individual.getAddress());
        data.put("phone", individual.getPhone());
        data.put("weight", individual.getWeight());
        data.put("sex", individual.getSex());
        data.put("cardno", individual.getCardno());
        //插入照片 文档中需要的域 前面要加上image 如： <<image:photo>>
        InputStream photo = new ClassPathResource("aspose/photo.png").getInputStream();
        data.put("photo", IOUtils.toByteArray(photo));

        //转换数据格式
        int len = data.size();
        String[] keys = data.keySet().toArray(new String[len]);
        Object[] values = data.values().toArray(new Object[len]);

        //填充数据
        document.getMailMerge().execute(keys, values);

        //设置http响应头
        response.setContentType("application/octet-stream");
        String fileName = new String("detail-详细数据.docx".getBytes("gb2312"), "ISO8859-1");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);

        //输出到流
        document.save(response.getOutputStream(), com.aspose.words.SaveFormat.DOCX);
    }

    /**
     * 下载模板
     */
    @GetMapping("/exportDetailWordTpl")
    public void exportDetailWordTpl(HttpServletResponse response) throws Exception {
        InputStream inputStream = new ClassPathResource("aspose/detail.docx").getInputStream();
        response.setContentType("application/octet-stream");
        String fileName = new String("detail-详细数据模板.docx".getBytes("gb2312"), "ISO8859-1");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        IOUtils.copy(inputStream, response.getOutputStream());
    }
}

